package com.jiang.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jiang.dao.IMemberDao;
import com.jiang.utils.DBUtils;
import com.jiang.vo.Member;

/**
 * @author Administrator comment on column member.mid is '邮箱或者手机号主键'; comment on
 *         column member.name is '姓名'; comment on column member.sex is '性别';
 *         comment on column member.edu is '学历'; comment on column
 *         member.birthday is '生日'; comment on column member.salary is '工资';
 *         comment on column member.note is '备注';
 * 
 */
public class MemberDaoImpl implements IMemberDao {

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.jiang.dao.IMemberDao#doCreate(com.jiang.vo.Member)
	 */
	private Connection conn;

	public MemberDaoImpl() {
		conn = DBUtils.getConnection();
	}

	@Override
	public Boolean doCreate(Member member) throws SQLException {

		String sql = " insert into member(mid,name,sex,edu,birthday,salary,note) values(?,?,?,?,?,?,?) ";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, member.getMid());
		ps.setString(2, member.getName());
		ps.setString(3, member.getSex());
		ps.setString(4, member.getEdu());
		ps.setDate(5, new java.sql.Date(member.getBirthday().getTime()));
		ps.setDouble(6, member.getSalary());
		ps.setString(7, member.getNote());
		return ps.executeUpdate() > 0;
	}

	@Override
	public List<Member> findAll() throws SQLException {

		List<Member> members = new ArrayList<Member>(10);
		String sql = "select mid,name,sex,edu,birthday,salary,note from member ";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {

			Member m = new Member();
			m.setMid(rs.getString(1));
			m.setName(rs.getString(2));
			m.setSex(rs.getString("sex"));
			m.setEdu(rs.getString("edu"));
			m.setBirthday(rs.getDate("birthday"));
			m.setSalary(rs.getDouble("salary"));
			m.setNote(rs.getString("note"));
			members.add(m);
		}
		return members;
	}

	@Override
	public Member findById(String id) throws SQLException {
		String sql = "select mid,name,sex,edu,birthday,salary,note from member where  mid = ? ";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, id);
		ResultSet rs = ps.executeQuery();
		Member m = null;
		while (rs.next()) {
			m = new Member();
			m.setMid(rs.getString(1));
			m.setName(rs.getString(2));
			m.setSex(rs.getString("sex"));
			m.setEdu(rs.getString("edu"));
			m.setBirthday(rs.getDate("birthday"));
			m.setSalary(rs.getDouble("salary"));
			m.setNote(rs.getString("note"));
		}
		return m;
	}

	/**
	 * 修改功能，我建议先删除后添加
	 */
	@Override
	public Boolean doUpdate(Member member) throws SQLException {

		String mid = member.getMid();
		Member _member = findById(mid);
		List<Member> re_list = new ArrayList<Member>(1);
		re_list.add(_member);
		// 先查询，然后删除，最后添加
		if (doBatchRemove(re_list)) {
			return doCreate(member);
		}
		return false;
	}

	/**
	 * 批量删除
	 */
	@Override
	public Boolean doBatchRemove(List<Member> members) throws SQLException {

		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < members.size(); i++) {
			sb.append("?,");
		}
		String ids = sb.delete(sb.length() - 1, sb.length()).toString();
		String sql = "delete from member where mid in (" + ids + ") ";
		PreparedStatement ps = conn.prepareStatement(sql);
		for (int i = 0; i < members.size(); i++) {
			ps.setString(i + 1, members.get(i).getMid());
		}
		return ps.executeUpdate() > 0;

	}

	/**
	 * 实现分页查询 column 查询列关键字 keyWord 查询输入的内容 currentPage 当前页 pageSize 每页显示的条数
	 */
	@Override
	public List<Member> findSplit(String column, String keyWord, int currentPage, int pageSize)
			throws SQLException {

		String sql = " select mid,name,sex,edu,birthday,salary,note from (select mid,name,sex,edu,birthday,salary,note,rownum as m from member where rownum<=? and "
				+ column + " like ? ) temp where temp.m>? ";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, currentPage * pageSize);
		ps.setString(2, "%" + keyWord + "%");
		ps.setInt(3, (currentPage - 1) * pageSize);
		List<Member> members = new ArrayList<Member>();
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Member m = new Member();
			m.setMid(rs.getString("mid"));
			m.setName(rs.getString("name"));
			m.setSex(rs.getString("sex"));
			m.setEdu(rs.getString("edu"));
			m.setBirthday(rs.getDate("birthday"));
			m.setSalary(rs.getDouble("salary"));
			m.setNote(rs.getString("note"));
			members.add(m);
		}
		return members;
	}

	@Override
	public List<Member> findByKey(String column, String keyWord) throws SQLException {
		
		String sql = "select mid,name,sex,edu,birthday,salary,note from member where  "+ column + " like ? ";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, "%" + keyWord + "%");
		List<Member> members = new ArrayList<Member>();
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Member m = new Member();
			m.setMid(rs.getString("mid"));
			m.setName(rs.getString("name"));
			m.setSex(rs.getString("sex"));
			m.setEdu(rs.getString("edu"));
			m.setBirthday(rs.getDate("birthday"));
			m.setSalary(rs.getDouble("salary"));
			m.setNote(rs.getString("note"));
			members.add(m);
		}
		return members;
	}

}
